#region Using

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;

using System.Data.SqlClient;
using System.Collections;
using System.Diagnostics;
using Gizmox.WebGUI.Common;
using Gizmox.WebGUI.Forms.Dialogs;
using Gizmox.WebGUI.Common.Resources;

using Gizmox.WebGUI.Forms;

#endregion

namespace Gizmox.WebGuiSampleApplication
{
    public partial class ExtendedListView : UserControl
    {
        

        #region Members
        
        private ImageList objImageList = null;
        private SqlConnection mobjConnection;
        private string mstrTableName = string.Empty;
        private string mstrOrderBy = string.Empty;
        private string mstrWhere = string.Empty;
        private string mstrSelect = string.Empty;

        private string mstrLastTableName = string.Empty;

        private int mintTop = 14;
        private int mintBottom = 0;
        private int mintTotalPages = 0;

        public event EventHandler OnSelectedIndexChanged;

        #endregion

        #region C'tor\D'tor
        
        public ExtendedListView()
        {
            InitializeComponent();


            this.listView1.View = View.Details;

            WinToWeb();

            this.listView1.SelectedIndexChanged += new System.EventHandler(this.listView1_SelectedIndexChanged);
         
            this.listView1.ColumnClick += new ColumnClickEventHandler(this.listView1_ColumnClick);

        }
        
        #endregion


        private void WinToWeb()
        {
            this.listView1.DataMember = null;
            this.listView1.ItemsPerPage = 14;
            this.listView1.TotalItems = 1;
            this.listView1.PagingChanged += new System.EventHandler(this.listView1_PagingChanged);

            this.blnReorder.Image = new IconResourceHandle("Columns.gif");
            this.blnSort.Image = new IconResourceHandle("Sorting.gif");
            this.btnAdd.Image = new IconResourceHandle("Add.gif");
            this.btnDelete.Image = new IconResourceHandle("Delete.GIF");
            this.button2.Image = new IconResourceHandle("Run.gif");//Search
            this.button1.Image = new IconResourceHandle("Run.gif");//Filter
        }


        #region SQL DataBase

        public SqlConnection Connection
        {
            get
            {
                if (mobjConnection == null)
                {
                    mobjConnection = new SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True");

                }
                return mobjConnection;
            }
            set
            {
                mobjConnection = value;
            }
        }

        private int GetTotalItems(string TableName, string WhereClause)
        {
            string strWhere = string.Empty;

            if (WhereClause != string.Empty)
            {

                if (WhereClause.Contains("WHERE"))
                {
                    strWhere = WhereClause;
                }
                else
                {
                    strWhere = " WHERE " + WhereClause;
                }
            }
            else
            {
                strWhere = string.Empty;
            }

            string strSQL = "select Count(*) from " + TableName + strWhere ;
            SqlCommand myCMD = new SqlCommand(strSQL, Connection);

            Connection.Close();//???
            if (Connection.State == ConnectionState.Closed)
            {
                Connection.Open();
            }

            SqlDataReader myReader = myCMD.ExecuteReader();

            while (myReader.Read())
            {

                return (int)myReader[0];
            }
            return 14;

        }

        //Create the record set
        public SqlDataReader LoadTable(string strSelect, string TableName, string WhereClause, string OrderBy)
        {
            string strOrderBy = string.Empty;
            string strSQL = string.Empty;
            mstrTableName = TableName;
            mstrOrderBy = OrderBy;
            mstrSelect = strSelect;



            if (WhereClause != string.Empty)
            {
               
                if (WhereClause.Contains("WHERE"))
                {
                    mstrWhere = WhereClause; 
                }
                else
                {
                    mstrWhere = " WHERE " + WhereClause;
                }
            }
            else
            {
                mstrWhere = string.Empty;
            }

            if (mstrOrderBy != string.Empty)
	        {
                mstrOrderBy = OrderBy;
                strOrderBy = " ORDER BY " + mstrOrderBy;
	        }

            if (mstrOrderBy != string.Empty)
            {
                strSQL = "WITH TempTable as(Select " + strSelect + ",Row_Number() OVER ( " + strOrderBy + ")as RowNumber from " + TableName + mstrWhere +
                    ")Select * from TempTable Where (RowNumber between " + mintBottom + " and " + mintTop + ")" ;

            }
            else
            {
                //Default/ init sql
                strSQL = "select Top 14 " + strSelect + " from " + TableName + mstrWhere + strOrderBy;
            }



            SqlCommand myCMD = new SqlCommand(strSQL, Connection);

            Connection.Close();//???
            if (Connection.State == ConnectionState.Closed)
            {
                Connection.Open();    
            }
            
            SqlDataReader myReader = myCMD.ExecuteReader();

            return myReader;

        }

        #endregion

        #region List

        public void LoadList(string strSelect, string strTableName, string strWhere, string strOrderBy,bool blnRefresh)
        {
            //Check to see if this table(+strWhere) has alredy been loaded
            //If so we don't have to go to the SQL server one more time
            //If blnRefresh is TRUE (On update ,Add ,Delete) ovvride it and run the SQL again
            if (strTableName != mstrTableName || strWhere != mstrWhere
                || mstrLastTableName == string.Empty || blnRefresh)
            {
                int intTotalItems = GetTotalItems(strTableName, strWhere);
                mintTotalPages = (int)Math.Round((intTotalItems / 14.0), MidpointRounding.AwayFromZero);

                SqlDataReader myReader = LoadTable(strSelect, strTableName, strWhere, strOrderBy);

                FillListView(myReader);
                
            }
            else
            {
                listView1_SelectedIndexChanged(this.listView1, EventArgs.Empty);
            }
           
        }

        public void FillListView(SqlDataReader myData)
        {
            ColumnHeader lvwColumn;
            ListViewItem itmListItem = null;

            //Clear items
            this.listView1.Items.Clear();

            //The first time  - Create the columns
            if (this.listView1.Columns.Count == 0 || mstrTableName != mstrLastTableName)
            {
                this.listView1.Columns.Clear();
                this.comboBox1.Items.Clear();

                //Image Column
                lvwColumn = new ColumnHeader();

                lvwColumn.Type = ListViewColumnType.Icon;
                lvwColumn.Image = new IconResourceHandle("Headers.Important.gif");
                lvwColumn.ContentAlign = ExtendedHorizontalAlignment.Center;

                lvwColumn.Text = "";

                lvwColumn.Width = 20;

                listView1.Columns.Add(lvwColumn);

                //Create the Columns
                for (int i = 0; i < myData.FieldCount; i++)
                {
                    lvwColumn = new ColumnHeader();
                    lvwColumn.Text = myData.GetName(i);

                    comboBox1.Items.Add(myData.GetName(i));

                    listView1.Columns.Add(lvwColumn);
                    
                }

                //Set the Columns width
                for (int i = 1; i < myData.FieldCount; i++)
                {
                    listView1.Columns[i].Width = (listView1.Width / myData.FieldCount - 1) - 11;
                }

               

                comboBox1.SelectedIndex = 0;
                
            }

            mstrLastTableName = mstrTableName ;

            //Set the order column - the first column
            mstrOrderBy = myData.GetName(0);

            //Fill the data
            while (myData.Read())
            {
                if (myData[0].ToString().Contains("N") || myData[0].ToString().Contains("3"))
                {
                    itmListItem = this.listView1.Items.Add(GetIcon("ImportantMail.gif"));

                    itmListItem.BackColor = Color.AntiqueWhite;

                }
                else if (myData[0].ToString().Contains("O") || myData[0].ToString().Contains("5"))
                {
                    itmListItem = this.listView1.Items.Add(GetIcon("AttachedMail.gif"));
                }
                else
                {
                    itmListItem = this.listView1.Items.Add(GetIcon("Font.gif"));
                }
                     
                

                for (int i = 0; i < listView1.Columns.Count-1; i++)
                {
                    if (myData.IsDBNull(i))
                    {
                        itmListItem.SubItems.Add("");
                    }
                    else
                    {
                        try
                        {
                            itmListItem.SubItems.Add(myData[i].ToString());
                        }
                        catch (Exception)
                        {
                        }
                        
                    }
                }
            }

            this.listView1.TotalPages = mintTotalPages; ;

            if (this.listView1.TotalPages !=0)
            {
                listView1.CurrentPage = mintTop / 14;    
            }
            this.listView1.UseInternalPaging = false;
               
        }

        private string GetIcon(string strName)
        {
            return (new IconResourceHandle(strName)).ToString();
        }

        public void LoadControl(string strTableName, string strFields)
        {
            LoadList(strFields, strTableName, string.Empty, string.Empty, false);
        }

        #endregion

        #region Properties

        private void Sort()
        {
            ListViewSortingOptions objListViewSortingOptions = new ListViewSortingOptions(this.listView1);
            objListViewSortingOptions.ShowDialog();
        }

        public void Search(string strText)
        {
            listView1.FindItemWithText(strText, true, 1);

            listView1.Update();

        }

        public bool ShowCheckBoxes
        {
            get
            {
                return this.listView1.CheckBoxes;
            }
            set
            {
                this.listView1.CheckBoxes = value;
            }
        }

        public void ReorderColumns()
        {
            ListViewColumnOptions objListViewColumnOptions = new ListViewColumnOptions(this.listView1);
            objListViewColumnOptions.ShowDialog();
        }

        [DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
        public int SelectedIndex
        {
            get
            {
                //The try catch is to be able to open this control in design time
                try
                {
                    return listView1.SelectedItems[0].Index;
                }
                catch
                {
                }

                return -1;
                
            }   
            set
            {

                if (value > -1)
                {
                    try
                    {
                        listView1.Items[value].Selected = true;
                    }
                    catch (Exception)
                    {
                        
                        
                    }
                    
                }
                
            }
            
        }

        public ListView.ColumnHeaderCollection Columns
        {
            get
            {
                return listView1.Columns;
            }
        }


        #endregion

        #region Events

        void listView1_PagingChanged(object sender, System.EventArgs e)
        {

            mintTop = listView1.CurrentPage * 14;
            mintBottom = mintTop - 14;

            LoadList(mstrSelect, mstrTableName, mstrWhere, mstrOrderBy, true);
        }

        void listView1_ColumnClick(object sender, ColumnClickEventArgs e)
        {
            string strSortOrder = string.Empty;

            if (listView1.Columns[e.Column].SortOrder == Gizmox.WebGUI.Forms.SortOrder.None || listView1.Columns[e.Column].SortOrder == Gizmox.WebGUI.Forms.SortOrder.Descending)
            {
                strSortOrder = "DESC";
            }
            else
            {
                strSortOrder = "ASC";
            }


            LoadList(mstrSelect, mstrTableName, mstrWhere, listView1.Columns[e.Column].Text + " " + strSortOrder, true);

        }

        //Filter
        private void button1_Click(object sender, EventArgs e)
        {
            if (textBox1.Text.Trim() != string.Empty)
            {
                LoadList(mstrSelect, mstrTableName, comboBox1.Text + " like '%" + textBox1.Text + "%'", mstrOrderBy, false);
            }
            else
            {
                LoadList(mstrSelect, mstrTableName, "", mstrOrderBy, false);
            }
        }

        //Search
        private void button2_Click(object sender, EventArgs e)
        {
            //ArrayList objArray = new ArrayList();
            //string strID = string.Empty;

            //if (strLastSearchField != comboBox1.Text || strLastSearchValue != textBox2.Text)
            //{
            //    intSearchIndex = 0;
            //    SqlDataReader objSqlDataReader = null;
            //    if (textBox2.Text.Trim() != string.Empty)
            //    {
            //        mintBottom = 0;
            //        mintTop = 100;
            //        objSqlDataReader = LoadTable(mstrSelect, mstrTableName, comboBox1.Text + " = '" + textBox2.Text + "'", mstrOrderBy);
            //    }

            //    strLastSearchField = comboBox1.Text;
            //    strLastSearchValue = textBox2.Text;




            //    //Fill the data
            //    while (objSqlDataReader.Read())
            //    {
            //        strID = objSqlDataReader[0].ToString();
            //        objArray.Add(strID);
            //    }

            //}



            //for (int i = 0; i < listView1.Items.Count; i++)
            //{
            //    if (listView1.Items[i].Text == objArray[intSearchIndex].ToString())
            //    {
            //        listView1.Items[i].BackColor = Color.Azure;
            //    }
            //}


            //intSearchIndex++;

        }

      

    


      

        private void listView1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (OnSelectedIndexChanged != null)
            {
                OnSelectedIndexChanged(sender, e);
            }
        }

        private void blnSort_Click(object sender, EventArgs e)
        {
            Sort();
        }

        private void blnReorder_Click(object sender, EventArgs e)
        {
            ReorderColumns();
        } 

        #endregion
       
    }
}